<?php

/*
 * To change this template, choose Tools | Templates
* and open the template in the editor.
*/

class HT_Model_administrator_models_seoreport extends Zend_Db_Table {//ten class fai viet hoa

	protected $_db;

	public function __construct() {
		$this->_name = "seo_page";
		$this->_db = Zend_Registry::get('dbMain');
		parent::init();
	}
		
	public function countSeoList($filter = array()) {
		$fromDate 	= trim($filter['from_date']);
		$toDate 	= trim($filter['to_date']);
		$sqlPlus = null;
		
		if($fromDate) $sqlPlus .= " AND date(date_updated) >= date('$fromDate')";
		if($toDate) $sqlPlus .= " AND date(date_updated) <= date('$toDate')";
		
		$sql 		= "SELECT COUNT(seopage_id) total,date(date_updated) date_updated
						FROM seo_page
						WHERE date_created != '0000-00-00 00:00:00' OR date_updated != '0000-00-00 00:00:00' $sqlPlus GROUP BY date(date_updated) ORDER BY date_updated";
		$seoData = $this->_db->fetchAll($sql);
		
		$seoList = array();
		foreach((array)$seoData as $seo){
			$seoList[$seo['date_updated']] = $seo['total'];
		}
		return $seoList;
	}
	
	public function countNewsList($filter = array()) {
		$fromDate 	= trim($filter['from_date']);
		$toDate 	= trim($filter['to_date']);
		$sqlPlus = null;
		
		if($fromDate) $sqlPlus .= " AND date(created) >= date('$fromDate')";
		if($toDate) $sqlPlus .= " AND date(created) <= date('$toDate')";
		
		$sql 		= "SELECT COUNT(id_news) total,date(created) created
						FROM news
						WHERE status='active' $sqlPlus GROUP BY date(created)  ORDER BY created";
		
		$newsData = $this->_db->fetchAll($sql);
		$newsList = array();
		foreach((array)$newsData as $news){
			$newsList[$news['created']] = $news['total'];
		}
		return $newsList;
	}
	
	public function countWorkingList($adminList,$dateList){
		$workingReport = array();
		foreach((array)$adminList as $adminId){
			$report = array();
			$report['adminName'] 	= $this->getAdminName($adminId);
			$report['data']			= $this->countWorkingData($adminId,$dateList);
			$workingReport[] 		= $report;
		}
		return $workingReport;
	}
	
	public function countWorkingData($adminId,$dateList) {
		$data 	= null;
		foreach((array)$dateList as $reportDate){
			$total1 = $this->countCreatePage($reportDate,$adminId);
			$total2 = $this->countUpdatePage($reportDate,$adminId);
			$data .= ($total1 + $total2).',';
		}
		return $data;
	}	
	
	private function getAdminName($adminId){
		$sql = "SELECT CONCAT(firstname,' ',lastname,' - ',user_name) fullname
				FROM user
				WHERE wishid = ?";
		return $this->_db->fetchOne($sql,array($adminId));
	}
	
	private function countCreatePage($reportDate,$adminId){
		$sql = "SELECT COUNT(seopage_id) total
				FROM seo_page
				WHERE date(date_created) = date(?) AND admin_create_id = ?";
		return $this->_db->fetchOne($sql,array($reportDate,$adminId));
	}
	
	private function countUpdatePage($reportDate,$adminId){
		$sql = "SELECT COUNT(seopage_id) total
				FROM seo_page
				WHERE date(date_updated) = date(?) AND admin_update_id = ? AND date(date_created) <> date(?)";
		return $this->_db->fetchOne($sql,array($reportDate,$adminId,$reportDate));
	}
}

?>
